package cn.dengta.webapp.trade.dao;

import java.math.BigDecimal;
import java.util.Date;
import java.util.List;

import cn.dengta.common.model.Status;
import cn.dengta.common.model.TimeInterval;
import cn.dengta.common.util.Formats;
import cn.dengta.common.web.Page;
import cn.dengta.context.model.WesKit;
import cn.dengta.webapp.base.dao.BaseDao;
import cn.dengta.webapp.base.util.MybatisUtil;
import cn.dengta.webapp.trade.dto.*;
import cn.dengta.webapp.trade.entity.UserIntegral;
import cn.dengta.webapp.trade.entity.UserIntegralLog;
import cn.dengta.webapp.trade.form.UserIntegralLogQuery;
import cn.dengta.webapp.trade.model.HandleStatus;
import cn.dengta.webapp.trade.model.TradeType;
import cn.dengta.webapp.trade.vo.IntegralDaySumVO;
import cn.dengta.webapp.user.entity.User;
import cn.dengta.webapp.user.model.BizType;
import cn.dengta.webapp.user.model.Gender;
import com.baomidou.mybatisplus.core.conditions.Wrapper;
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.baomidou.mybatisplus.core.enums.SqlKeyword;
import me.codeplayer.util.*;
import org.apache.ibatis.annotations.*;
import org.apache.ibatis.jdbc.SQL;

import static cn.dengta.webapp.trade.entity.UserIntegralLog.*;

/**
 * 积分流水记录表 Mapper 接口
 *
 * @author yuqidi
 * @since 2022-03-12
 */
public interface UserIntegralLogDao extends BaseDao<UserIntegralLog> {

	@Select("SELECT MAX(id) AS id, user_id, SUM(trade_amount) AS tradeAmount FROM tr_user_integral_log" +
			" WHERE trade_type ${tradeTypes} AND add_time <= #{endTime} AND handle_status = 0" +
			" GROUP BY user_id")
	List<UserIntegralLog> findFrozenIncomeGroup(@Param("tradeTypes") String tradeTypes, @Param("endTime") Date endTime);

	@Update("UPDATE tr_user_integral_log SET handle_status = 9" +
			" WHERE trade_type ${tradeTypes} AND add_time < #{endTime} AND handle_status = 0")
	Integer makeIncomeActive(@Param("tradeTypes") String tradeTypes, @Param("endTime") Date endTime);

	/**
	 * 查询指定用户、指定交易时间段、指定交易类型(可变参数)的交易数据键/值对(交易类型/交易金额)即可
	 */
	@SelectProvider(type = UserIntegralLogSQL.class, method = "getSumOfTradeAmountGroupByType")
	List<UserIntegralLogSumDTO> getSumOfTradeAmountGroupByType(@Param("query") UserIntegralLogQuery query, @Param("interval") TimeInterval addTimeInterval, @Param("tradeTypes") TradeType... tradeTypes);

	/**
	 * 查询指定用户时间段内记录
	 */
	@SelectProvider(type = UserIntegralLogSQL.class, method = "findByGender")
	List<UserIntegralLog> findByGender(@Param("interval") TimeInterval addTimeInterval, @Param("gender") Gender gender, @Param("liveIds") Long[] liveIds, @Param("tradeTypes") TradeType... tradeTypes);

	/**
	 * 查询指定用户时间段内记录
	 */
	@SelectProvider(type = UserIntegralLogSQL.class, method = "findUserIdByGender")
	List<Long> findUserIdByGender(@Param("interval") TimeInterval addTimeInterval, @Param("gender") Gender gender, @Param("liveIds") Long[] liveIds, Boolean forLive, @Param("tradeTypes") TradeType... tradeTypes);

	@Select("SELECT * FROM tr_user_integral_log log JOIN dt_user u ON u.id = log.user_id ${ew.customSqlSegment} ")
	Page<UserIntegralLog> findPage(Page<UserIntegralLog> page, @Param("ew") QueryWrapper<UserIntegralLog> wrapper);

	/**
	 * 获取指定流水
	 */
	@SelectProvider(type = UserIntegralLogSQL.class, method = "findUserPage")
	Page<UserIntegralLog> findUserPage(Page<UserIntegralLog> page, @Param("query") UserIntegralLogQuery query, @Param("interval") TimeInterval addTimeInterval, @Param("tradeTypes") TradeType... tradeTypes);

	/** 获取用户周收入数据 */
	@Select("SELECT user_id, agent_id, agent2_id, SUM(trade_amount) trade_amount, live_id FROM tr_user_integral_log WHERE trade_type = 'INTEGRAL_GAIN' AND add_time BETWEEN #{st} AND #{et} AND flow = 1 AND agent_id IS NOT NULL AND ${key} GROUP BY user_id, agent_id, agent2_id")
	List<UserIntegralLog> findAgentSumByTime(@Param("st") Date startTime, @Param("et") Date endTime, @Param("key") String key);

	/**
	 * 关联推广员营收Id
	 */
	@Select("UPDATE tr_user_integral_log SET agent_reward_id = #{agentRewardId} WHERE user_id IN (${userIds}) AND live_id ${liveId} AND flow = 1 AND add_time BETWEEN #{st} AND #{et} AND trade_type = 'INTEGRAL_GAIN' AND agent_id IS NOT NULL")
	void relationAgentRewardId(@Param("userIds") String userIds, @Param("agentRewardId") Long agentRewardId, @Param("liveId") String liveId, @Param("st") Date startTime, @Param("et") Date endTime);

	@Select("UPDATE tr_user_integral_log SET handle_status = -1 WHERE user_id IN (${userIds}) AND live_id ${liveId} AND flow = 1 AND add_time BETWEEN #{st} AND #{et} AND trade_type = 'INTEGRAL_GAIN' AND agent_id IS NOT NULL AND agent_reward_id IS NULL")
	void freezeAgentReward(@Param("userIds") String userIds, @Param("liveId") String liveId, @Param("st") Date startTime, @Param("et") Date endTime);

	/**
	 * 查询指定用户、指定交易时间段、指定交易类型(可变参数)的交易数据
	 */
	@SelectProvider(type = UserIntegralLogSQL.class, method = "findSumOfAgent")
	List<UserIntegralLogSumDTO> findSumOfAgent(@Param("query") UserIntegralLogQuery query, @Param("interval") TimeInterval addTimeInterval, boolean showAll, boolean userFromOrTo, boolean fromSource, @Param("tradeTypes") TradeType... tradeTypes);

	/**
	 * 查询指定用户、指定交易时间段、指定交易类型(可变参数)的交易数据
	 */
	@SelectProvider(type = UserIntegralLogSQL.class, method = "findPageSumOfAgent")
	Page<UserIntegralLogSumDTO> findPageSumOfAgent(Page<UserIntegralLog> page, @Param("query") UserIntegralLogQuery query, @Param("interval") TimeInterval addTimeInterval, boolean fromSource, @Param("tradeTypes") TradeType... tradeTypes);

	@SelectProvider(type = UserIntegralLogSQL.class, method = "findChannelIncome")
	List<UserIntegralLogSumDTO> findChannelIncome(@Param("interval") TimeInterval addTimeInterval, @Param("gender") Gender gender, @Param("query") UserIntegralLogQuery query, @Param("tradeTypes") TradeType... tradeTypes);

	/** 获取时间范围内数量 */
	@SelectProvider(type = UserIntegralLogSQL.class, method = "getCountByAddTime")
	List<UserIntegralLog> getCountByAddTime(@Param("wesKit") WesKit wesKit, @Param("userIds") Long[] userIds, @Param("gender") Gender gender, @Param("interval") TimeInterval addTimeInterval, @Param("minIntegral") BigDecimal minIntegral, @Param("tradeTypes") TradeType... tradeTypes);

	/** 获取音视频收入 */
	@Select("SELECT user_id AS userId, SUM(trade_amount) AS totals, DATE(add_time) AS addDate FROM tr_user_integral_log WHERE trade_type ='INTEGRAL_GAIN' AND coin_trade_type IN('COIN_FOR_VIDEO','COIN_FOR_AUDIO') AND add_time BETWEEN #{interval.finalBegin} AND #{interval.finalEnd} GROUP BY user_id , addDate")
	List<UserIntegralSumDTO> findUserSum(@Param("interval") TimeInterval timeInterval);

	@Select("SELECT user_id, SUM(trade_amount) AS totalAmount FROM tr_user_integral_log" +
			" ${ew.customSqlSegment}" +
			" GROUP BY user_id")
	List<UserIntegral> findIncomeSum(@Param("ew") Wrapper<?> wrapper);

	@Select("""
			 SELECT
			 IFNULL(SUM(IF(coin_trade_type='COIN_FOR_MSG', trade_amount, 0)), 0) AS chatIncome,
				IFNULL(SUM(IF(coin_trade_type='COIN_FOR_VIDEO', trade_amount, 0)),0) AS mediaTotalIncome,
				IFNULL(SUM(IF(coin_trade_type='COIN_FOR_GIFT' || coin_trade_type='COIN_FOR_GIFT_BAG', trade_amount, 0)),0) AS giftTotalIncome
				FROM tr_user_integral_log
			${ew.customSqlSegment}""")
	UserIntegralIncomeDTO sumUserIncome(@Param("ew") Wrapper<?> wrapper);

	@Select("""
			 SELECT
			 IFNULL(SUM(IFNULL(trade_amount, 0)),0) as speedVideoIncome
				from tr_user_integral_log log join im_media_log mlog on mlog.id = log.assoc_id and mlog.scene in (2,3)
			${ew.customSqlSegment} """)
	UserIntegralIncomeDTO sumUserSpeedVideoIncome(@Param("ew") Wrapper<?> wrapper);

	@Select("""
			 SELECT
			 IFNULL(SUM(IFNULL(trade_amount, 0)),0) as speedGiftIncome
				from tr_user_integral_log log join tr_gift_log glog on glog.id = log.assoc_id and glog.channel_name is not null
			${ew.customSqlSegment} """)
	UserIntegralIncomeDTO sumUserSpeedGiftIncome(@Param("ew") Wrapper<?> wrapper);

	@Select("""
            SELECT DATE_FORMAT(add_time, '%Y-%m-%d') AS day,SUM(ABS(trade_amount)) AS total FROM tr_user_integral_log
            ${ew.customSqlSegment}
            GROUP BY day
			""")
	List<IntegralDaySumVO> findDaySumTotal(@Param("ew") Wrapper<?> wrapper);

	@Select("SELECT IFNULL(SUM(trade_amount), 0) AS total FROM tr_user_integral_log WHERE live_id = #{liveId}")
	BigDecimal getTotalIntegralByLiveId(@Param("liveId") Long liveId);

	@Select("""
            SELECT  SUM(log.agent_gain_integral) totalTradeAmount  FROM tr_user_income_daily_log log
            WHERE (log.agent_id = #{agentId} OR log.root_agent_id = #{agentId}) AND log.add_time BETWEEN #{interval.finalBegin} AND #{interval.finalEnd}
			""")
	BigDecimal findTotalAgentAmount(@Param("agentId") Long agentId, @Param("interval") TimeInterval interval);

	class UserIntegralLogSQL {

		public String getSumOfTradeAmountGroupByType(@Param("query") UserIntegralLogQuery query, @Param("interval") TimeInterval addTimeInterval, @Param("tradeTypes") TradeType... tradeTypes) {
			SQL sql = new SQL().SELECT("user_id userId, trade_type tradeType,agent_id agentId,agent2_id agent2Id," +
					" coin_trade_type coinTradeType, COUNT(DISTINCT(user_id)) userCount, COUNT(coin_trade_type) coinTypeCount, IFNULL(SUM(trade_amount),0) totalTradeAmount" + StringUtil.toString(query.selectKey)).FROM("tr_user_integral_log");
			MybatisUtil.appendEqSql(sql, FLOW, query.getFlow(), "#{flow}");
			MybatisUtil.appendInIdSql(sql, USER_ID, query.userIds);
			MybatisUtil.appendInStrSql(sql, TRADE_TYPE, tradeTypes);
			MybatisUtil.appendSql(sql, HANDLE_STATUS, SqlKeyword.GE, HandleStatus.INIT.getValue(), HandleStatus.INIT.getValue().toString());
			MybatisUtil.appendSqlInterval(sql, ADD_TIME, addTimeInterval, null);
			if (query.getBizType() != null) {
				sql.WHERE(BizType.CHAT == query.getBizType() ? "live_id IS NULL" : "live_id IS NOT NULL");
			}
			sql.GROUP_BY(StringUtil.notEmpty(query.groupAttr) ? query.groupAttr : (X.isValid(tradeTypes) ? TRADE_TYPE : USER_ID));
			return sql.toString();
		}

		public String findChannelIncome(@Param("interval") TimeInterval addTimeInterval, @Param("gender") Gender gender, @Param("query") UserIntegralLogQuery query, @Param("tradeTypes") TradeType... tradeTypes) {
			SQL sql = new SQL()
					.SELECT("log.user_id userId, log.trade_type tradeType, COUNT(DISTINCT log.to_user_id) toUserCount, COUNT(DISTINCT log.user_id) userCount, IFNULL(SUM(log.trade_amount),0) totalTradeAmount")
					.FROM("tr_user_integral_log log")
					.JOIN("dt_user u ON u.id = log.user_id AND u.channel_id IS NOT NULL");
			MybatisUtil.appendEqSql(sql, "u.gender", gender, "#{gender}");

			final String prefix = "log.";
			MybatisUtil.appendInStrSql(sql, prefix + TRADE_TYPE, tradeTypes);
			if (query != null) {
				MybatisUtil.appendEqSql(sql, "u.reg_country", query.getRegCountry(), "#{query.regCountry}");
				MybatisUtil.appendInIdSql(sql, prefix + USER_ID, query.userIds);
				MybatisUtil.appendEqSql(sql, "u.channel_id", query.getChannelId(), "#{query.channelId}");
				MybatisUtil.appendInIdSql(sql, "u.channel_id", query.channelIds);
				sql.GROUP_BY(StringUtil.notEmpty(query.groupKey) ? query.groupKey : prefix + USER_ID);
				if (StringUtil.notEmpty(query.getRegCountry()) && Status.asResult(query.queryChannelCountry)) {
					sql.JOIN("dt_channel c ON c.id = log.channel_id AND c.campaign_name LIKE '" + query.getRegCountry() + "/%'");
				}
			}
			MybatisUtil.appendSql(sql, prefix + HANDLE_STATUS, SqlKeyword.GE, HandleStatus.INIT.getValue(), HandleStatus.INIT.getValue().toString());
			MybatisUtil.appendSqlInterval(sql, prefix + ADD_TIME, addTimeInterval, null);
			MybatisUtil.appendSqlInterval(sql, "u.add_time", addTimeInterval, null);
			return sql.toString();
		}

		public String findSumOfAgent(@Param("query") UserIntegralLogQuery query, @Param("interval") TimeInterval addTimeInterval, boolean showAll, boolean userFromOrTo, boolean fromSource, @Param("tradeTypes") TradeType... tradeTypes) {
			return findAgentInfo(query, addTimeInterval, showAll, userFromOrTo, fromSource, false, tradeTypes);
		}

		public String findPageSumOfAgent(Page<UserIntegralLog> page, @Param("query") UserIntegralLogQuery query, @Param("interval") TimeInterval addTimeInterval, boolean fromSource, @Param("tradeTypes") TradeType... tradeTypes) {
			return findAgentInfo(query, addTimeInterval, false, true, fromSource, true, tradeTypes);
		}

		private String findAgentInfo(UserIntegralLogQuery query, TimeInterval addTimeInterval, boolean showAll, boolean userFromOrTo, boolean fromSource, boolean isPage, TradeType... tradeTypes) {
			final String prefix = "log.";
			StringBuilder sb = new StringBuilder(600)
					.append("log.user_id userId," +
							" log.trade_type tradeType," +
							" log.coin_trade_type coinTradeType," +
							" COUNT(DISTINCT(log.user_id)) userCount," +
							" COUNT(log.coin_trade_type) coinTypeCount," +
							//总收益
							" IFNULL(SUM(log.trade_amount),0) totalTradeAmount," +
							" SUM(IF(log.coin_trade_type = 'COIN_FOR_GREET', log.trade_amount, 0)) greetIntegral," +
							" SUM(IF(log.coin_trade_type = 'COIN_FOR_MSG', log.trade_amount, 0)) msgIntegral," +
							" SUM(IF(log.coin_trade_type = 'COIN_FOR_AUDIO', log.trade_amount, 0)) audioIntegral," +
							" SUM(IF(log.coin_trade_type = 'COIN_FOR_VIDEO', log.trade_amount, 0)) videoIntegral," +
							" SUM(IF(log.coin_trade_type = 'COIN_FOR_GIFT', log.trade_amount, 0)) giftIntegral," +
							" SUM(IF(log.coin_trade_type = 'COIN_FOR_GIFT_BAG', log.trade_amount, 0)) giftBagIntegral," +
							" SUM(IF(log.coin_trade_type = 'COIN_FOR_GIFT_LIVE', log.trade_amount, 0)) giftLiveIntegral");
			if (fromSource) {
				//累计流水平台钻
				sb.append(",ABS(IFNULL(SUM(ucLog.trade_amount),0)) totalTradeCoin,ABS(IFNULL(SUM(ucLog.trade_free_part),0)) totalFreeTradeCoin," +
						" ABS(SUM(IF(log.coin_trade_type = 'COIN_FOR_GREET', ucLog.trade_amount, 0))) greetCoin," +
						" ABS(SUM(IF(log.coin_trade_type = 'COIN_FOR_MSG', ucLog.trade_amount, 0))) msgCoin," +
						" ABS(SUM(IF(log.coin_trade_type = 'COIN_FOR_AUDIO', ucLog.trade_amount, 0))) audioCoin," +
						" ABS(SUM(IF(log.coin_trade_type = 'COIN_FOR_VIDEO', ucLog.trade_amount, 0))) videoCoin," +
						" ABS(SUM(IF(log.coin_trade_type = 'COIN_FOR_GIFT', ucLog.trade_amount, 0))) giftCoin," +
						" ABS(SUM(IF(log.coin_trade_type = 'COIN_FOR_GIFT_LIVE', ucLog.trade_amount, 0))) giftLiveCoin");
			}
			SQL sql = new SQL()
					.SELECT(sb.append(StringUtil.toString(query.selectKey)).toString())
					.FROM("tr_user_integral_log log")
					.JOIN("dt_user u ON u.id = log.user_id");
			if (fromSource) {
				sql.LEFT_OUTER_JOIN("tr_user_coin_log ucLog ON ucLog.id = log.to_user_coin_log_id");
			}

			boolean fromExtractUser = X.isValid(tradeTypes) && X.size(tradeTypes) == 1 && ArrayUtil.ins(tradeTypes[0], TradeType.INTEGRAL_GAIN_EXTRACT_USER, TradeType.INTEGRAL_INVITE_GAIN);
			Integer level = query.getAgentLevel();
			if (!fromExtractUser && query.joinAgent) {
				sql.WHERE("u.agent_id IS NOT NULL");
				if (!showAll) {
					sql.WHERE("log.add_time > u.agent_join_time" + (level == null ? " AND (u.agent_id = log.agent_id OR u.agent_id = log.agent2_id)" : ""));
				}
			}
			if (level == null) {
				if (X.isValid(query.getAgentIds())) {
					sql.WHERE(showAll ? MybatisUtil.inIdSql("u.agent_id", query.getAgentIds()) : "(" + MybatisUtil.inIdSql(prefix + AGENT_ID, query.getAgentIds()) + " OR " + MybatisUtil.inIdSql(prefix + AGENT2_ID, query.getAgentIds()) + ")");
				}
			} else {
				String levelAttr = level == 1 ? "log.agent_id" : "log.agent2_id";
				sql.WHERE("u.agent_id = " + levelAttr);
				if (level == 1) {
					sql.WHERE("log.agent2_id IS NULL");
				}
				if (X.isValid(query.getAgentIds())) {
					if (level == 0) {
						sql.JOIN("dt_agent at ON at.id = u.agent_id");
					}
					MybatisUtil.appendInIdSql(sql, level == 0 ? "at.parent_id" : levelAttr, query.getAgentIds());
				}
			}
			MybatisUtil.appendEqSql(sql, prefix + FLOW, query.getFlow(), "#{query.flow}");
			MybatisUtil.appendInStrSql(sql, prefix + TRADE_TYPE, tradeTypes);
			MybatisUtil.appendInIdSql(sql, prefix + (userFromOrTo ? USER_ID : TO_USER_ID), query.getUserIds());
			MybatisUtil.appendSql(sql, prefix + HANDLE_STATUS, SqlKeyword.GE, HandleStatus.INIT.getValue(), HandleStatus.INIT.getValue().toString());
			MybatisUtil.appendSqlInterval(sql, prefix + ADD_TIME, addTimeInterval, null);
			boolean hasGroupAttr = StringUtil.notEmpty(query.groupAttr);
			sql.GROUP_BY(hasGroupAttr ? query.groupAttr : (X.isValid(tradeTypes) ? prefix + TRADE_TYPE : prefix + USER_ID));
			sql.ORDER_BY((hasGroupAttr ? (isPage ? query.groupAttr + " desc, SUM(log.trade_amount) desc, " : query.groupAttr + " desc, ") : "") + prefix + ID);
			return sql.toString();
		}

		public String findByGender(@Param("interval") TimeInterval addTimeInterval, @Param("gender") Gender gender, @Param("liveIds") Long[] liveIds, @Param("tradeTypes") TradeType... tradeTypes) {
			SQL sql = new SQL().SELECT("log.*").FROM("tr_user_integral_log log");
			if (gender != null) {
				sql.JOIN("dt_user u ON log.user_id = u.id")
						.WHERE("u.gender = #{gender}");
			}
			final String prefix = "log.";
			MybatisUtil.appendInStrSql(sql, prefix + TRADE_TYPE, tradeTypes);
			MybatisUtil.appendInStrSql(sql, prefix + LIVE_ID, liveIds);
			MybatisUtil.appendSqlInterval(sql, prefix + ADD_TIME, addTimeInterval, null);
			sql.ORDER_BY(prefix + ID);
			return sql.toString();
		}

		public String findUserIdByGender(@Param("interval") TimeInterval addTimeInterval, @Param("gender") Gender gender, @Param("liveIds") Long[] liveIds, Boolean forLive, @Param("tradeTypes") TradeType... tradeTypes) {
			SQL sql = new SQL().SELECT("DISTINCT log.user_id").FROM("tr_user_integral_log log");
			if (gender != null) {
				sql.JOIN("dt_user u ON log.user_id = u.id")
						.WHERE("u.gender = #{gender}");
			}
			final String prefix = "log.";
			MybatisUtil.appendInStrSql(sql, prefix + TRADE_TYPE, tradeTypes);
			MybatisUtil.appendInStrSql(sql, prefix + LIVE_ID, liveIds);
			MybatisUtil.appendSqlInterval(sql, prefix + ADD_TIME, addTimeInterval, null);
			if (forLive != null) {
				sql.WHERE("log.live_id" + (forLive ? " IS NOT NULL" : " IS NULL"));
			}
			sql.ORDER_BY(prefix + ID);
			return sql.toString();
		}

		public String getCountByAddTime(@Param("wesKit") WesKit wesKit, @Param("userIds") Long[] userIds, @Param("gender") Gender gender, @Param("interval") TimeInterval addTimeInterval, @Param("minIntegral") BigDecimal minIntegral, @Param("tradeTypes") TradeType... tradeTypes) {
			SQL sql = new SQL().SELECT("log.user_id userId, sum(log.trade_amount) tradeAmount").FROM("tr_user_integral_log log");
			final String prefix = "log.";
			if (gender != null) {
				sql.JOIN("dt_user u ON log.user_id = u.id");
				MybatisUtil.appendEqSql(sql, "u.gender", gender, "#{gender}");
			}
			sql.WHERE("log.flow = 1");
			if (wesKit != null) {
				//过滤，根据userId和 WesKit的最小和最大值
				sql.WHERE("log.user_id BETWEEN " + wesKit.getMinId() + " AND " + wesKit.maxId());
			}
			MybatisUtil.appendInStrSql(sql, prefix + TRADE_TYPE, tradeTypes);
			MybatisUtil.appendInIdSql(sql, prefix + USER_ID, userIds);
			MybatisUtil.appendSqlInterval(sql, prefix + ADD_TIME, addTimeInterval, null);
			sql.GROUP_BY(prefix + USER_ID);
			if (minIntegral != null) {
				sql.HAVING("SUM(log.trade_amount) >= #{minIntegral}");
			}
			return sql.toString();
		}

		public String findUserPage(Page<UserIntegralLog> page, @Param("query") UserIntegralLogQuery query, @Param("interval") TimeInterval addTimeInterval, @Param("tradeTypes") TradeType... tradeTypes) {
			SQL sql = new SQL().SELECT("log.*").FROM("tr_user_integral_log log");
			final String prefix = "log.";
			MybatisUtil.appendEqSql(sql, prefix + FLOW, query.getFlow(), "#{query.flow}");
			MybatisUtil.appendEqSql(sql, prefix + USER_ID, query.getUserId(), "#{query.userId}");
			MybatisUtil.appendEqSql(sql, prefix + TO_USER_ID, query.getToUserId(), "#{query.toUserId}");
			MybatisUtil.appendEqSql(sql, prefix + TRADE_TYPE, query.getTradeType(), "#{query.tradeType}");
			if (StringUtil.notEmpty(query.getUserNickname()) || query.getAgentId() != null) {
				sql.JOIN("dt_user u ON log.user_id = u.id");
				MybatisUtil.appendSql(sql, "u.nickname", SqlKeyword.LIKE, query.getUserNickname(), StringUtil::notEmpty, "${query.userNickname}");
				MybatisUtil.appendEqSql(sql, "u." + User.AGENT_ID, query.getAgentId(), "#{query.agentId}");
			}
			TimeInterval liveInterval = query.getLiveInterval();
			if (liveInterval != null) {
				sql.WHERE("log.live_id in (SELECT r.id FROM im_live r WHERE r.status < 0 AND r.duration >= 60 AND r.end_Time >= '" + Formats.formatDatetime(liveInterval.getFinalBegin()) + "' AND r.end_Time <= '" + Formats.formatDatetime(liveInterval.getFinalEnd()) + (query.getUserId() != null ? "' AND r.user_id =" + query.getUserId() + ")" : "')"));
			}
			MybatisUtil.appendInStrSql(sql, prefix + TRADE_TYPE, tradeTypes);
			MybatisUtil.appendSqlInterval(sql, prefix + ADD_TIME, addTimeInterval, null);
			sql.ORDER_BY("log.id desc");
			return sql.toString();
		}

	}

}
